/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.dao;

import com.kallsonys.service.BilledMont;
import com.kallsonys.service.ElementListPage;
import com.kallsonys.service.Order;
import com.kallsonys.service.OrderItem;
import com.kallsonys.service.OrderQuote;
import com.kallsonys.service.CityOrder;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class OrderDAO {

    private static String CREATE_ORDER_OMS = "{ call PKG_CURDS.CREATE_ORDER_OMS (?, ?, ?, ?, ?, ?, ?, ?) }";
    private static String CREATE_ORDERITEM = "{ call PKG_CURDS.CREATE_ORDERITEM (?, ?, ?, ?, ?) }";
    private static String UPDATE_ORDER_OMS = "{ call PKG_CURDS.UPDATE_ORDER_OMS (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }";
    private static String GET_ORDER_OMS = "{ call PKG_CURDS.GET_ORDER_OMS (?, ?) }";
    private static String GET_ORDERITEM_ORDER = "{ call PKG_CURDS.GET_ORDERITEM_ORDER (?, ?) }";
    private static String CREATE_ORDERQUOTE = "{ call PKG_CURDS.CREATE_ORDERQUOTE (?, ?, ?, ?, ?, ?) }";
    private static String UPDATE_ORDERQUOTE = "{ call PKG_CURDS.UPDATE_ORDERQUOTE (?, ?, ?, ?, ?, ?) }";
    private static String GET_ORDERQUOTE_ORDER = "{ call PKG_CURDS.GET_ORDERQUOTE_ORDER (?, ?) }";
    private static String GET_OPEN_ORDERS_BY_CLIENT = "{ call PKG_CURDS.GET_OPEN_ORDERS_BY_CLIENT (?, ?) }";
    private static String GET_ORDERS_OMS_BY_PRODUCT = "{ call PKG_CURDS.GET_ORDERS_OMS_BY_PRODUCT (?, ?) }";
    private static String GET_TOTAL_BILLED_BY_MONTH = "{ call PKG_CURDS.GET_TOTAL_BILLED_BY_MONTH (?, ?, ?, ?) }";
    private static String GET_OPEN_ORDERS = "{ call PKG_CURDS.GET_OPEN_ORDERS (?, ?, ?, ?) }";
    private static String GET_CLOSED_MOST_BILLED_ORDERS = "{ call PKG_CURDS.GET_CLOSED_MOST_BILLED_ORDERS (?, ?, ?, ?, ?, ?) }";
    private static String GET_ORDER_ADDRESDETAIL = "{ call PKG_CURDS.GET_ORDER_ADDRESDETAIL (?, ?) }";
    private static String UPDATE_ORDER_STATUS = "{ call PKG_CURDS.UPDATE_ORDER_STATUS (?, ?, ?) }";
    
    
    
    private DataSource ds;

    public OrderDAO(DataSource ds) {
        this.ds = ds;
    }

    public long CreateOrder(Order order) throws Exception {
        long retorno = 0;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_ORDER_OMS);

            stmt.setLong(1, order.getCustomerId());
            stmt.setLong(2, order.getCreditCardId());
            stmt.setLong(3, order.getPrice());
            stmt.setInt(4, order.getSource());
            stmt.setInt(5, order.getStatus());
            stmt.setString(6, order.getComments());
            stmt.setLong(7, order.getAddressId());
            stmt.registerOutParameter(8, OracleTypes.INTEGER);
            stmt.execute();

            //Obtengo el Id de la Orden
            retorno = stmt.getLong(8);

            //Recorro los Items
            for (OrderItem item : order.getItems()) {
                stmt = con.prepareCall(CREATE_ORDERITEM);

                stmt.setLong(1, item.getProductId());
                stmt.setLong(2, retorno);
                stmt.setLong(3, item.getItemPrice());
                stmt.setInt(4, item.getItemQuantity());
                stmt.registerOutParameter(5, OracleTypes.INTEGER);
                stmt.execute();
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public void UpdateOrder(Order order) throws Exception {
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(UPDATE_ORDER_OMS);

            stmt.setLong(1, order.getId());
            stmt.setLong(2, order.getCustomerId());
            stmt.setLong(3, order.getCreditCardId());
            stmt.setLong(4, order.getPrice());
            stmt.setInt(5, order.getSource());
            stmt.setInt(6, order.getStatus());
            stmt.setString(7, order.getComments());
            stmt.setLong(8, order.getAddressId());
            stmt.setInt(9, order.getShippingCompany());
            stmt.setDate(10, new java.sql.Date(order.getEstimatedDelivery().getTime()));
            stmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return;
    }
    
    public void UpdateOrderStatus(long idOrder, int Status, Date estimatedDelivery) throws Exception {
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(UPDATE_ORDER_STATUS);

            stmt.setLong(1, idOrder);
            stmt.setInt(2, Status);
            stmt.setDate(3, new java.sql.Date(estimatedDelivery.getTime()));
            stmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return;
    }

    public Order getById(long idOrder) throws Exception {
        Order retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_ORDER_OMS);
            stmt.setLong(1, idOrder);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    retorno = new Order();
                    retorno.setId(rs.getLong("ORDERID"));
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setComments(rs.getString("ORDERCOMMENTS"));
                    retorno.setCreditCardId(rs.getLong("CREDITCARDID"));
                    retorno.setCustomerId(rs.getLong("CUSTOMERID"));
                    retorno.setEstimatedDelivery(rs.getDate("ESTIMATEDDELIVERY"));
                    retorno.setPrice(rs.getLong("ORDERPRICE"));
                    retorno.setShippingCompany(rs.getInt("SHIPPINGCOMPANYID"));
                    retorno.setSource(rs.getInt("ORDERSOURCE"));
                    retorno.setStatus(rs.getInt("ORDERSTATUS"));
                }
            }

            if (retorno != null) {
                stmt = con.prepareCall(GET_ORDERITEM_ORDER);
                stmt.setLong(1, idOrder);
                stmt.registerOutParameter(2, OracleTypes.CURSOR);
                stmt.execute();
                List<OrderItem> lstItems = new ArrayList<OrderItem>();

                o = stmt.getObject(2);
                if (o instanceof ResultSet) {
                    rs = (ResultSet) o;

                    while (rs.next()) {
                        OrderItem item = new OrderItem();
                        item.setId(rs.getLong("ORDERITEMID"));
                        item.setItemPrice(rs.getLong("ITEMPRICE"));
                        item.setItemQuantity(rs.getInt("ITEMQUANTITY"));
                        item.setOrderId(rs.getLong("ORDERID"));
                        item.setProductId(rs.getLong("PRODUCTID"));
                        lstItems.add(item);
                    }
                    retorno.setItems(lstItems);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public BilledMont getBilledMonth(int year, int month) throws Exception {
        BilledMont retorno = new BilledMont();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_TOTAL_BILLED_BY_MONTH);
            stmt.setLong(1, year);
            stmt.setLong(2, month);
            stmt.registerOutParameter(3, OracleTypes.NUMBER);
            stmt.registerOutParameter(4, OracleTypes.NUMBER);
            stmt.execute();

            retorno.setTotalBilled(stmt.getLong(3));
            retorno.setTotalOrders(stmt.getInt(4));

            //Object o = stmt.getObject(2);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public List<Order> getOpenOrdersByIdCustomer(long idCustomer) throws Exception {
        List<Order> LstRetorno = new ArrayList<Order>();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        ResultSet rsItems = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_OPEN_ORDERS_BY_CLIENT);
            stmt.setLong(1, idCustomer);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    Order retorno = new Order();
                    retorno.setId(rs.getLong("ORDERID"));
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setComments(rs.getString("ORDERCOMMENTS"));
                    retorno.setCreditCardId(rs.getLong("CREDITCARDID"));
                    retorno.setCustomerId(rs.getLong("CUSTOMERID"));
                    retorno.setEstimatedDelivery(rs.getDate("ESTIMATEDDELIVERY"));
                    retorno.setPrice(rs.getLong("ORDERPRICE"));
                    retorno.setShippingCompany(rs.getInt("SHIPPINGCOMPANYID"));
                    retorno.setSource(rs.getInt("ORDERSOURCE"));
                    retorno.setStatus(rs.getInt("ORDERSTATUS"));

                    stmt = con.prepareCall(GET_ORDERITEM_ORDER);
                    stmt.setLong(1, retorno.getId());
                    stmt.registerOutParameter(2, OracleTypes.CURSOR);
                    stmt.execute();
                    List<OrderItem> lstItems = new ArrayList<OrderItem>();

                    o = stmt.getObject(2);
                    if (o instanceof ResultSet) {
                        rsItems = (ResultSet) o;

                        while (rsItems.next()) {
                            OrderItem item = new OrderItem();
                            item.setId(rsItems.getLong("ORDERITEMID"));
                            item.setItemPrice(rsItems.getLong("ITEMPRICE"));
                            item.setItemQuantity(rsItems.getInt("ITEMQUANTITY"));
                            item.setOrderId(rsItems.getLong("ORDERID"));
                            item.setProductId(rsItems.getLong("PRODUCTID"));
                            lstItems.add(item);
                        }
                        retorno.setItems(lstItems);
                    }
                    LstRetorno.add(retorno);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return LstRetorno;
    }

    public List<Order> getOrdersByIdProduct(long idProduct) throws Exception {
        List<Order> LstRetorno = new ArrayList<Order>();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        ResultSet rsItems = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_ORDERS_OMS_BY_PRODUCT);
            stmt.setLong(1, idProduct);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    Order retorno = new Order();
                    retorno.setId(rs.getLong("ORDERID"));
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setComments(rs.getString("ORDERCOMMENTS"));
                    retorno.setCreditCardId(rs.getLong("CREDITCARDID"));
                    retorno.setCustomerId(rs.getLong("CUSTOMERID"));
                    retorno.setEstimatedDelivery(rs.getDate("ESTIMATEDDELIVERY"));
                    retorno.setPrice(rs.getLong("ORDERPRICE"));
                    retorno.setShippingCompany(rs.getInt("SHIPPINGCOMPANYID"));
                    retorno.setSource(rs.getInt("ORDERSOURCE"));
                    retorno.setStatus(rs.getInt("ORDERSTATUS"));

                    stmt = con.prepareCall(GET_ORDERITEM_ORDER);
                    stmt.setLong(1, retorno.getId());
                    stmt.registerOutParameter(2, OracleTypes.CURSOR);
                    stmt.execute();
                    List<OrderItem> lstItems = new ArrayList<OrderItem>();

                    o = stmt.getObject(2);
                    if (o instanceof ResultSet) {
                        rsItems = (ResultSet) o;

                        while (rsItems.next()) {
                            OrderItem item = new OrderItem();
                            item.setId(rsItems.getLong("ORDERITEMID"));
                            item.setItemPrice(rsItems.getLong("ITEMPRICE"));
                            item.setItemQuantity(rsItems.getInt("ITEMQUANTITY"));
                            item.setOrderId(rsItems.getLong("ORDERID"));
                            item.setProductId(rsItems.getLong("PRODUCTID"));
                            lstItems.add(item);
                        }
                        retorno.setItems(lstItems);
                    }
                    LstRetorno.add(retorno);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return LstRetorno;
    }
    
    
    public ElementListPage<Order> getOpenOrders(int page) throws Exception {
        ElementListPage<Order> LstRetorno = new ElementListPage<Order>();

        LstRetorno.setElementList(new ArrayList<Order>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        ResultSet rsItems = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_OPEN_ORDERS);
            stmt.setLong(1, page);
            stmt.setLong(2, 10);
            stmt.registerOutParameter(3, OracleTypes.NUMBER);
            stmt.registerOutParameter(4, OracleTypes.CURSOR);
            stmt.execute();
            //Total Items
            LstRetorno.setPages(stmt.getInt(3) / 10 );

            Object o = stmt.getObject(4);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    Order retorno = new Order();
                    retorno.setId(rs.getLong("ORDERID"));
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setComments(rs.getString("ORDERCOMMENTS"));
                    retorno.setCreditCardId(rs.getLong("CREDITCARDID"));
                    retorno.setCustomerId(rs.getLong("CUSTOMERID"));
                    retorno.setEstimatedDelivery(rs.getDate("ESTIMATEDDELIVERY"));
                    retorno.setPrice(rs.getLong("ORDERPRICE"));
                    retorno.setShippingCompany(rs.getInt("SHIPPINGCOMPANYID"));
                    retorno.setSource(rs.getInt("ORDERSOURCE"));
                    retorno.setStatus(rs.getInt("ORDERSTATUS"));

                    stmt = con.prepareCall(GET_ORDERITEM_ORDER);
                    stmt.setLong(1, retorno.getId());
                    stmt.registerOutParameter(2, OracleTypes.CURSOR);
                    stmt.execute();
                    List<OrderItem> lstItems = new ArrayList<OrderItem>();

                    o = stmt.getObject(2);
                    if (o instanceof ResultSet) {
                        rsItems = (ResultSet) o;

                        while (rsItems.next()) {
                            OrderItem item = new OrderItem();
                            item.setId(rsItems.getLong("ORDERITEMID"));
                            item.setItemPrice(rsItems.getLong("ITEMPRICE"));
                            item.setItemQuantity(rsItems.getInt("ITEMQUANTITY"));
                            item.setOrderId(rsItems.getLong("ORDERID"));
                            item.setProductId(rsItems.getLong("PRODUCTID"));
                            lstItems.add(item);
                        }
                        retorno.setItems(lstItems);
                    }
                    LstRetorno.getElementList().add(retorno);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return LstRetorno;
    }
    

    public List<Order> getOpenOrders() throws Exception {
        List<Order> arrRetorno = new ArrayList<Order>();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_OPEN_ORDERS);
            stmt.setLong(1, 0);
            stmt.setLong(2, 80000);
            stmt.registerOutParameter(3, OracleTypes.NUMBER);
            stmt.registerOutParameter(4, OracleTypes.CURSOR);
            stmt.execute();
            //Total Items
             

            Object o = stmt.getObject(4);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    Order retorno = new Order();
                    retorno.setId(rs.getLong("ORDERID"));
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setComments(rs.getString("ORDERCOMMENTS"));
                    retorno.setCreditCardId(rs.getLong("CREDITCARDID"));
                    retorno.setCustomerId(rs.getLong("CUSTOMERID"));
                    retorno.setEstimatedDelivery(rs.getDate("ESTIMATEDDELIVERY"));
                    retorno.setPrice(rs.getLong("ORDERPRICE"));
                    retorno.setShippingCompany(rs.getInt("SHIPPINGCOMPANYID"));
                    retorno.setSource(rs.getInt("ORDERSOURCE"));
                    retorno.setStatus(rs.getInt("ORDERSTATUS"));
                    arrRetorno.add(retorno);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return arrRetorno;
    }
    
    public ElementListPage<Order> getClosedOrders(int page, Date startDate, Date endDate) throws Exception {
        ElementListPage<Order> LstRetorno = new ElementListPage<Order>();

        LstRetorno.setElementList(new ArrayList<Order>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        ResultSet rsItems = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_CLOSED_MOST_BILLED_ORDERS);
            stmt.setDate(1, new java.sql.Date(startDate.getTime()));
            stmt.setDate(2, new java.sql.Date(endDate.getTime()));
            stmt.setInt(3, page);
            stmt.setInt(4, 10);
            stmt.registerOutParameter(5, OracleTypes.NUMBER);
            stmt.registerOutParameter(6, OracleTypes.CURSOR);
            stmt.execute();
            //Total Items
            LstRetorno.setPages(stmt.getInt(5) / 10 );

            Object o = stmt.getObject(6);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    Order retorno = new Order();
                    retorno.setId(rs.getLong("ORDERID"));
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setComments(rs.getString("ORDERCOMMENTS"));
                    retorno.setCreditCardId(rs.getLong("CREDITCARDID"));
                    retorno.setCustomerId(rs.getLong("CUSTOMERID"));
                    retorno.setEstimatedDelivery(rs.getDate("ESTIMATEDDELIVERY"));
                    retorno.setPrice(rs.getLong("ORDERPRICE"));
                    retorno.setShippingCompany(rs.getInt("SHIPPINGCOMPANYID"));
                    retorno.setSource(rs.getInt("ORDERSOURCE"));
                    retorno.setStatus(rs.getInt("ORDERSTATUS"));

                    stmt = con.prepareCall(GET_ORDERITEM_ORDER);
                    stmt.setLong(1, retorno.getId());
                    stmt.registerOutParameter(2, OracleTypes.CURSOR);
                    stmt.execute();
                    List<OrderItem> lstItems = new ArrayList<OrderItem>();

                    o = stmt.getObject(2);
                    if (o instanceof ResultSet) {
                        rsItems = (ResultSet) o;

                        while (rsItems.next()) {
                            OrderItem item = new OrderItem();
                            item.setId(rsItems.getLong("ORDERITEMID"));
                            item.setItemPrice(rsItems.getLong("ITEMPRICE"));
                            item.setItemQuantity(rsItems.getInt("ITEMQUANTITY"));
                            item.setOrderId(rsItems.getLong("ORDERID"));
                            item.setProductId(rsItems.getLong("PRODUCTID"));
                            lstItems.add(item);
                        }
                        retorno.setItems(lstItems);
                    }
                    LstRetorno.getElementList().add(retorno);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return LstRetorno;
    }


    public long CreateOrderQuote(OrderQuote orderQuote) throws Exception {
        long retorno = 0;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_ORDERQUOTE);

            stmt.setInt(1, orderQuote.getSupplierId());
            stmt.setLong(2, orderQuote.getOrderId());
            stmt.setInt(3, orderQuote.isExistences() ? 1 : 0);
            stmt.setLong(4, orderQuote.getPrice());
            stmt.setInt(5, orderQuote.isSelected() ? 1 : 0);
            stmt.registerOutParameter(6, OracleTypes.INTEGER);
            stmt.execute();

            //Obtengo el Id de la Orden
            retorno = stmt.getLong(6);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
        return retorno;
    }

    public void UpdateOrderQuote(OrderQuote orderQuote) throws Exception {

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(UPDATE_ORDERQUOTE);

            stmt.setLong(1, orderQuote.getId());
            stmt.setInt(2, orderQuote.getSupplierId());
            stmt.setLong(3, orderQuote.getOrderId());
            stmt.setLong(4, orderQuote.isExistences() ? 1 : 0);
            stmt.setLong(5, orderQuote.getPrice());
            stmt.setLong(6, orderQuote.isSelected() ? 1 : 0);
            stmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
        return;
    }

    public List<OrderQuote> getOrderQuoteById(long idOrder) throws Exception {
        List<OrderQuote> retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_ORDERQUOTE_ORDER);
            stmt.setLong(1, idOrder);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;
                retorno = new ArrayList<OrderQuote>();
                while (rs.next()) {
                    OrderQuote item = new OrderQuote();
                    item.setId(rs.getLong("ORDERQUOTEID"));
                    item.setExistences(rs.getInt("HASEXISTENCES") == 1);
                    item.setOrderId(rs.getLong("ORDERID"));
                    item.setPrice(rs.getLong("QUOTEPRICE"));
                    item.setSelected(rs.getInt("ISSELECTED") == 1);
                    item.setSupplierId(rs.getInt("SUPPLIERID"));

                    retorno.add(item);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
    
     public CityOrder getCityByOrder(long orderId) throws Exception {
        CityOrder retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();

            stmt = con.prepareCall(GET_ORDER_ADDRESDETAIL);
            stmt.setLong(1, orderId);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    retorno = new CityOrder();
                    retorno.setAddressId(rs.getLong("ADDRESSID"));
                    retorno.setAddressName(rs.getString("ADDRESSSTREET"));
                    retorno.setCityId(rs.getLong("CITYID"));
                    retorno.setCityName(rs.getString("CITYNAME"));
                    retorno.setCountryId(rs.getLong("COUNTRYID"));
                    retorno.setCountryName(rs.getString("COUNTRYNAME"));
                    retorno.setStateId(rs.getLong("COUNTRYID"));
                    retorno.setStateName(rs.getString("CITYNAME"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
}
